package action;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import bean.User;
import util.HibernateUtil;

/**
 * 本类测试HQL的预处理指令
 * 
 * HIbernate中的预编译与Spring的预编译的处理差不多。
 * 
 * 1：使用标准的?
 * 
 * 　 2：使用命名参数
 * 
 * 　　 2.1：使用名称逐个设置。
 * 
 * 　　 2.2：使用Map(key的值等于参数的名称)
 * 
 * 　　 2.3：使用Bean(属性的名称等于参数的名称）
 * 
 * @author 半颗柠檬、
 * 
 */
public class Query_HQL_Prepared {

	public static void main(String[] args) {
		// Query_HQL_Prepared.one(); // 使用标准的 ？
		// Query_HQL_Prepared.two(); //使用名称逐个设置。
//		Query_HQL_Prepared.three(); // 使用Map(key的值等于参数的名称)
		Query_HQL_Prepared.four();   //使用Bean(属性的名称等于参数的名称）
	}



	/*
	 * 使用标准的？
	 */
	private static void one() {
		Session session = null;
		Transaction tran = null;
		Query query = null;
		String hql = "";
		try {

			session = HibernateUtil.getSession();
			tran = session.beginTransaction();
			hql = " select u from User u where userid<=? and userid>=? and username like ?";

			/**
			 * HQL的索引是从0开始的
			 */
			query = session.createQuery(hql);
			query.setParameter(0, 6);
			query.setParameter(1, 1);
			query.setParameter(2, "%user%");

			List<User> userList = query.list();

			for (User user : userList) {

				System.out.println("username=" + user.getUsername()
						+ "\t userid=" + user.getUserid());

			}

			tran.commit();
		} catch (Exception e) {
			tran.rollback();
			e.printStackTrace();

		} finally {
			HibernateUtil.closeSession();
		}

	}

	/**
	 * 使用名称逐个设置
	 */
	private static void two() {
		Session session = null;
		Query query = null;
		Transaction tran = null;
		String sql = "";
		try {

			session = HibernateUtil.getSession();
			tran = session.beginTransaction();
			sql = "  select u from User u where username like :username and  userid>= :userid_begin  and  userid<= :userid_end ";

			query = session.createQuery(sql);
			query.setString("username", "%user%");
			query.setInteger("userid_begin", 3);
			query.setInteger("userid_end", 5);

			List<User> userList = query.list();

			for (User user : userList) {

				System.out.println("username=" + user.getUsername()
						+ "\t userid=" + user.getUserid());

			}

			tran.commit();
		} catch (Exception e) {
			e.printStackTrace();
			tran.rollback();
		} finally {
			HibernateUtil.closeSession();
		}

	}

	/**
	 * 使用Map(key的值等于参数的名称)
	 */
	private static void three() {

		Session session = null;
		Transaction tran = null;
		Query query = null;
		String sql = "";

		try {
			session=HibernateUtil.getSession();
			tran=session.beginTransaction();
			
			sql=" select  u from User u where userid>= :userid_begin and userid<= :userid_end and username like :username";
			query=session.createQuery(sql);
			
			Map<String,Object> sqlMap=new HashMap<String,Object>();
			sqlMap.put("userid_begin", 3);
			sqlMap.put("userid_end",5);
			sqlMap.put("username", "%user%");
			
			query.setProperties(sqlMap);
			
			List<User> userList = query.list();

			for (User user : userList) {

				System.out.println("username=" + user.getUsername()
						+ "\t userid=" + user.getUserid());

			}
			
			tran.commit();
		} catch (Exception e) {
			e.printStackTrace();
			tran.rollback();
		} finally {
			HibernateUtil.closeSession();
		}

	}

	
	/**
	 * 使用Bean(属性的名称等于参数的名称）
	 */
	private static void four() {
	
		Session session = null;
		Query query = null;
		Transaction tran = null;
		String sql="";
		
		try {
			session=HibernateUtil.getSession();
			tran=session.beginTransaction();
			
			sql=" select u from User u  where username like :username and userid>= :userid_begin and  userid<= :userid_end  "; 
			
			User user=new User();
			user.setUsername("%user%");
			user.setUserid_begin(3);   //User bean中一定要有userid_begin和userid_end属性，且要有get和set方法
			user.setUserid_end(5);
			
			query=session.createQuery(sql);
			query.setProperties(user);
			
			List<User> userList = query.list();

			for (User use1r : userList) {

				System.out.println("username=" + use1r.getUsername()
						+ "\t userid=" + use1r.getUserid());

			}
			
			
			tran.commit();
		} catch (Exception e) {
			e.printStackTrace();
			tran.rollback();
		}finally{
			HibernateUtil.closeSession();
		}
	
		
	}
	
}
